package com.szcinda.call.utils;

import com.szcinda.call.repository.*;
import com.szcinda.call.service.OrderDetailDto;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.util.StringUtils;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

public class PoiUtil {

    // 将数据导出成excel文件
    public static ResponseEntity<byte[]> exportOrderDetail(List<OrderDetailDto> orders) {
        HttpHeaders headers = null;
        ByteArrayOutputStream baos = null;
        try {
            //1.创建Excel文档
            HSSFWorkbook workbook = new HSSFWorkbook();
            //2.创建文档摘要
            workbook.createInformationProperties();
            //3.获取文档信息，并配置
            DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
            //4.获取摘要信息并配置
            SummaryInformation si = workbook.getSummaryInformation();
            //4.1设置文档主题
            si.setSubject("货代数据表");
            //创建Excel表单
            HSSFSheet sheet = workbook.createSheet("货代数据");
            //创建日期显示格式
            HSSFCellStyle dateCellStyle = workbook.createCellStyle();
            dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd HH:mm:ss"));
            //创建标题的显示样式
            HSSFCellStyle headerStyle = workbook.createCellStyle();
//            headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //定义列的宽度
//            sheet.setColumnWidth(0, 5 * 256);
//            sheet.setColumnWidth(1, 12 * 256);
//            sheet.setColumnWidth(2, 10 * 256);
//            sheet.setColumnWidth(3, 5 * 256);
//            sheet.setColumnWidth(4, 12 * 256);
            //5.设置表头
            String[] headerNames = new String[]{
                    "船公司", "航线", "船名", "航次", "订舱号", "提单号", "出货地", "起运港", "卸货港", "最终目的地", "箱型",
                    "体积", "箱号", "封签号", "是否有预配", "码头", "箱号", "中文船名", "箱型", "持箱人",
                    "状态", "进场方式", "进场时间", "出场方式", "出场时间", "运抵", "海放", "码放", "海事", "国检", "外理",
                    "配载", "装货港", "卸货港", "目的港", "转运港", "场箱位", "箱皮重", "箱货重", "VGM码头称重", "VGM船公司",
                    "VGM报文接收时间", "运抵报文时间", "进出口", "件数", "重量", "体积", "报关单号", "海关放行", "船名航次",
                    "计划靠泊时间", "实际靠泊时间", "计划离泊时间", "实际离泊时间", "取消"};
            HSSFRow headerRow = sheet.createRow(0);
            for (int i = 0; i < headerNames.length; i++) {
                HSSFCell cell0 = headerRow.createCell(i);
                cell0.setCellValue(headerNames[i]);
            }
            //6.装数据
            int rowCount = 1;
            for (int i = 0; i < orders.size(); i++) {
                HSSFRow row = sheet.createRow(rowCount);
                OrderDetailDto order = orders.get(i);
                setMainData(row, order);
                List<OrderDetailDto.GhzhDto> dtos = order.getGhzhDtos();
                if (dtos.size() > 0){
                    for (int j = 0; j < dtos.size(); j++) {
                        OrderDetailDto.GhzhDto dto = dtos.get(j);
                        if (j == 0) {
                            setGhzhData(row, dto);
                        }else{
                            HSSFRow nextRow = sheet.createRow(rowCount);
                            setMainData(nextRow, order);
                            setGhzhData(nextRow, dto);
                        }
                        rowCount++;
                    }
                }else {
                    rowCount++;
                }
            }
            headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment",
                    new String("货代数据表.xls".getBytes("UTF-8"), "iso-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            baos = new ByteArrayOutputStream();
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }

    private static void setMainData(HSSFRow row, OrderDetailDto order){
        row.createCell(0).setCellValue(order.getCarrier());
        row.createCell(1).setCellValue(order.getService());
        row.createCell(2).setCellValue(order.getVessel());
        row.createCell(3).setCellValue(order.getVoyage());
        row.createCell(4).setCellValue(order.getBookingNumber());
        row.createCell(5).setCellValue(order.getPickingNumber());
        row.createCell(6).setCellValue(order.getPlr());
        row.createCell(7).setCellValue(order.getPol());
        row.createCell(8).setCellValue(order.getPod());
        row.createCell(9).setCellValue(order.getFinalDestination());
        row.createCell(10).setCellValue(order.getSize());
        row.createCell(11).setCellValue(order.getVolume());
        row.createCell(12).setCellValue(order.getContainerNo());
        row.createCell(13).setCellValue(order.getSealNo());
        row.createCell(14).setCellValue(order.getEir());
    }

    private static void setGhzhData(HSSFRow row, OrderDetailDto.GhzhDto dto) {
        GhzhInfo ghzhInfo = dto.getGhzhInfo();
        row.createCell(15).setCellValue(ghzhInfo.getTerm());
        row.createCell(16).setCellValue(ghzhInfo.getCntrNo());
        row.createCell(17).setCellValue(StringUtils.isEmpty(ghzhInfo.getSecondVslVoy()) ? ghzhInfo.getFirstVslVoy() : ghzhInfo.getSecondVslVoy());
        row.createCell(18).setCellValue(ghzhInfo.getVntType());
        row.createCell(19).setCellValue(ghzhInfo.getCntCoper());
        row.createCell(20).setCellValue(ghzhInfo.getCntStatus());
        row.createCell(21).setCellValue(ghzhInfo.getInyardOpmode());
        row.createCell(22).setCellValue(ghzhInfo.getInyardDateTime());
        row.createCell(23).setCellValue(ghzhInfo.getOutyardOpmode());
        row.createCell(24).setCellValue(ghzhInfo.getOutyardDateTime());
        row.createCell(25).setCellValue(ghzhInfo.getCostcoFg());
        row.createCell(26).setCellValue(ghzhInfo.getCuspassfg());
        row.createCell(27).setCellValue(ghzhInfo.getDockPassfg());
        row.createCell(28).setCellValue(ghzhInfo.getMsapassfg());
        row.createCell(29).setCellValue(ghzhInfo.getGjPassfg());
        row.createCell(30).setCellValue(ghzhInfo.getWlpassfg());
        row.createCell(31).setCellValue(ghzhInfo.getCntrStowagefg());
        GhzhDetail ghzhDetail = dto.getGhzhDetail();
        if (ghzhDetail != null) {
            row.createCell(32).setCellValue(ghzhDetail.getLoadingPort());
            row.createCell(33).setCellValue(ghzhDetail.getDischargePort());
            row.createCell(34).setCellValue(ghzhDetail.getDestinationPort());
            row.createCell(35).setCellValue(ghzhDetail.getTransport());
            row.createCell(36).setCellValue(ghzhDetail.getYardSpace());
            row.createCell(37).setCellValue(ghzhDetail.getCaseWeight());
            row.createCell(38).setCellValue(ghzhDetail.getContainerWeight());
            row.createCell(39).setCellValue(ghzhDetail.getVgmWeight());
            row.createCell(40).setCellValue(ghzhDetail.getVgmShip());
            row.createCell(41).setCellValue(ghzhDetail.getVgmReceiveTime());
            row.createCell(42).setCellValue(ghzhDetail.getShipToTime());
        }
        GhzhGoods ghzhGoods = dto.getGhzhGoods();
        if (ghzhGoods != null) {
            row.createCell(43).setCellValue(ghzhGoods.getVoyIefg());
            row.createCell(44).setCellValue(ghzhGoods.getCntrGoodsPackages());
            row.createCell(45).setCellValue(ghzhGoods.getCntrGoodsWeight());
            row.createCell(46).setCellValue(ghzhGoods.getCntrGoodsVolume());
            row.createCell(47).setCellValue(ghzhGoods.getEntryNo());
            row.createCell(48).setCellValue(ghzhGoods.getCustomPassfgnm());
        }
        GhzhShip ghzhShip = dto.getGhzhShip();
        if (ghzhShip != null) {
            row.createCell(49).setCellValue(ghzhShip.getScdChnName());
            row.createCell(50).setCellValue(ghzhShip.getSbtPsttm());
            row.createCell(51).setCellValue(ghzhShip.getSbtAsttm());
            row.createCell(52).setCellValue(ghzhShip.getSbtPedtm());
            row.createCell(53).setCellValue(ghzhShip.getSbtAedtm());
            row.createCell(54).setCellValue(ghzhShip.getIsCanceled());
        }
    }
}
